package com.bw.dao;

import com.bw.bean.Sugar;
import com.bw.bean.SugerDTO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface SugarDao {
    @Select("select b.name,sum(o.final_total_amount) `value` from order_info o \n" +
            "LEFT JOIN base_province b ON o.province_id=b.id\n" +
            "GROUP BY b.`name` ")
    List<Sugar> getList1();

    @Select("select t.`name`,sum(o.final_total_amount) `value` from order_info o \n" +
            "LEFT JOIN order_detail d ON o.id=d.order_id\n" +
            "LEFT JOIN sku_info s ON d.sku_id=s.id\n" +
            "LEFT JOIN base_trademark t ON s.tm_id=t.id\n" +
            "GROUP BY t.`name`")
    List<Sugar> getList2();

    @Select("select c1.`name`,sum(o.benefit_reduce_amount) `value` from order_info O\n" +
            "LEFT JOIN order_detail d ON o.id=d.order_id\n" +
            "LEFT JOIN sku_info s ON d.sku_id=s.id\n" +
            "LEFT JOIN base_category3 c3 ON s.category3_id=c3.id\n" +
            "LEFT JOIN base_category2 c2 ON c3.category2_id=c2.id\n" +
            "LEFT JOIN base_category1 c1 ON c2.category1_id=c1.id\n" +
            "where c1.name is not null GROUP BY c1.`name` ")
    List<Sugar> getList3();

    @Select("select * from\n" +
            "(select count(*) 'num1' from user_info where TIMESTAMPDIFF(month,create_time,NOW()) < 14)a,\n" +
            "(select count(*) 'num2' from user_info where TIMESTAMPDIFF(month,create_time,NOW()) > 14)b")
    List<SugerDTO> getList4();
}
